package org.df.ulab.bot.redmine;

import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.df.ulab.bot.api.pmi.entity.Activity;
import org.df.ulab.bot.api.pmi.entity.Issue;
import org.df.ulab.bot.api.pmi.entity.IssuePriority;
import org.df.ulab.bot.api.pmi.entity.IssueTimeEntry;
import org.df.ulab.bot.api.pmi.entity.IssueType;
import org.df.ulab.bot.api.pmi.entity.Member;
import org.df.ulab.bot.api.pmi.entity.Project;
import org.df.ulab.bot.plugins.pmi.HandlerSession;
import org.df.ulab.bot.plugins.pmi.commons.PmiDatabase;
import org.df.ulab.bot.plugins.pmi.commons.PmiSettings;
import org.df.ulab.bot.redmine.entity.RedActivity;
import org.df.ulab.bot.redmine.entity.RedIssue;
import org.df.ulab.bot.redmine.entity.RedMember;
import org.df.ulab.bot.redmine.entity.RedProject;
import org.df.ulab.bot.redmine.entity.RedType;
import org.df.ulab.util.db.DBAccess;
import org.df.ulab.util.db.MySqlDB;
import org.df.ulab.util.db.PostgreSQLDB;
import org.df.ulab.util.db.SQLUpdateException;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * @author Daniel Filgueiras
 * 04/05/2011
 */
public class RedmineDB extends PmiDatabase {

	private static final Logger logger = LoggerFactory.getLogger(RedmineDB.class);
	private static Lock lock = new ReentrantLock();
	private static RedmineDB db;
	
	private DBAccess dbAccess;
	
	public static void main(String[] args) {
		getInstance().connect();
		
	}
	
	public static RedmineDB getInstance() {
		lock.lock();
		if (db == null) {
			db = new RedmineDB();
		}
		lock.unlock();
		
		return db;
	}

	public boolean connect() {
		lock.lock();
		try {
			if (dbAccess == null) {
				logger.info("Creating connection to database");
				
				PmiSettings settings = HandlerSession.getPmtSettings();
				
				if (settings.getDbHost() == null || settings.getDbLogin() == null || settings.getDbPassword() == null)
					return false;
				
				switch(settings.getDbType()) {
				case MySql:
					dbAccess = new MySqlDB(
							settings.getDbHost(),
							settings.getDbName(),
							settings.getDbLogin(),
							settings.getDbPassword());
					break;
				case PosgreSql:
					dbAccess = new PostgreSQLDB(
							settings.getDbHost(),
							settings.getDbName(),
							settings.getDbLogin(),
							settings.getDbPassword());
					break;
				}
			}
			logger.info("Testing connection");
			return dbAccess.testConnection();
		} catch (Exception e){
			logger.error("Could not connect to database",e);
		} finally {
			lock.unlock();
		}
		return false;
	}
	
	/**
	 * Database Iteration
	 */
	protected Issue loadIssue(Long id) {
		
		logger.debug("Loading issue id: {}", id);
		Map<String, Object> item = dbAccess.executeMapQuery("SELECT * FROM issues WHERE id = ? ORDER BY id", id);
		
		if (item == null || item.size() == 0)
			return null;
		
		RedIssue issue = new RedIssue(
				(Integer)item.get("id"), 
				(String)item.get("subject"));
		
		// Spend Time
		Double hours = loadIssueSpendTime(issue);
		if (hours != null)
			issue.setSpendTime(hours);
		else
			issue.setSpendTime(0D);
		
		issue.setProject(getProjectFromCache(item.get("project_id").toString()));
		issue.setAuthor(getMemberFromCache(item.get("author_id").toString()));
		issue.setAssignedMember(getMemberFromCache(item.get("assigned_to_id").toString()));
		
//		String categoryId = item.get("category_id").toString();
//		String statusId = item.get("status_id").toString();
//		String priorityId = item.get("status_id").toString();
		
		return issue;
	}
	
	private Double loadIssueSpendTime(Issue issue) {
		logger.debug("Loading time entries for issue: {}", issue.getId());
		return dbAccess.executeScalarQuery(
				"SELECT sum(hours) FROM time_entries WHERE issue_id = ?", issue.getId());
	}
	
	protected Set<Long> loadIssues(Member member) {
		List<Long> ids = dbAccess.executeListScalarQuery(
				"SELECT issues.id FROM issues inner join issue_statuses on (issue_statuses.id = issues.status_id) WHERE assigned_to_id = ? and is_closed = 0", member.getId());
		
		return new HashSet<Long>(ids);
	}
	
	protected Set<Project> loadProjects() {

		logger.debug("Loading all projects...");
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("SELECT * FROM projects");
		if (result == null)
			return null;
		
		HashSet<Project> projects = new HashSet<Project>();
		for (Map<String, Object> item : result) {
			RedProject project = new RedProject(item.get("id").toString());
			project.setName(item.get("name").toString());

			projects.add(project);
		}
		logger.debug("Got {} projects", projects.size());
		
		return projects;
	}
	
	protected Set<Member> loadMembers() {
		Set<Member> members = new HashSet<Member>();
		
		logger.debug("Loading all members...");
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("SELECT * FROM users WHERE type = 'User' and status = '1'");
		if (result == null)
			return null;
		
		for (Map<String, Object> item : result) {
			Integer id = (Integer) item.get("id");
			RedMember member = new RedMember(id.toString());
			member.setName(item.get("firstname").toString() + " " + item.get("lastname").toString());
			member.setLogin(item.get("login").toString());
			member.setMail(item.get("mail").toString());
			
			members.add(member);
		}
		logger.debug("Got {} members", members.size());
		
		return members;
	}
	
	@Override
	protected Set<Activity> loadActivities() {
		
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("select * from enumerations where type = 'TimeEntryActivity' and active = 1 order by position");
		
		if (result == null)
			return null;
		
		Set<Activity> activities = new HashSet<Activity>();
		
		for (Map<String, Object> item : result) {
			Number number = (Number)item.get("id");
			RedActivity activity = new RedActivity(number.toString());
			activity.setName(item.get("name").toString());
			
			activities.add(activity);
		}
		
		return activities;
	}
	
	@Override
	protected Set<IssueType> loadIssueTypes() {
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("select * from trackers order by position");
		
		if (result == null)
			return null;
		
		Set<IssueType> issueTypes = new HashSet<IssueType>();
		
		for (Map<String, Object> item : result) {
			RedType type = new RedType(item.get("id").toString());
			type.setName(item.get("name").toString());
			
			issueTypes.add(type);
		}
		
		return issueTypes;
	}

	@Override
	public Issue commitWork(IssueTimeEntry timeEntry) {
		try {
			DateTime now = new DateTime(); 
			
			RedIssue redIssue = (RedIssue)timeEntry.getIssue();
			boolean updated = dbAccess.executeUpdate(
					"INSERT INTO time_entries " +
					"(project_id, user_id, issue_id, hours, comments," +
					" activity_id, spent_on, tyear, tmonth, tweek, created_on, updated_on) VALUES" +
					"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
					Integer.valueOf(redIssue.getProject().getId()),
					Integer.valueOf(timeEntry.getMember().getId()),
					redIssue.getId(),
					timeEntry.getWorkedTime(),
					timeEntry.getComment() != null ? timeEntry.getComment().toString() : null,
					Integer.valueOf(timeEntry.getActivity().getId()),
					now.toDate(),
					now.getYear(),
					now.getMonthOfYear(),
					now.getWeekyear(),
					now.toDate(),
					now.toDate());
			
			
			if (updated) {
				redIssue.setSpendTime(redIssue.getSpendTime() + timeEntry.getWorkedTime());
				return redIssue;
			}
		} catch (SQLUpdateException e) {
			logger.error("Could not update",e);
			return null;
		}
		//project_id, user_id, issue_id, hours, comments, activity_id, spent_on, tyear, tmonth, tweek, creater_on, updated_on
		return timeEntry.getIssue();
	}

	@SuppressWarnings("unchecked")
	@Override
	protected Set<IssuePriority> loadIssuePriorities() {
		// TODO Auto-generated method stub
		return Collections.EMPTY_SET;
	}
	
}
